﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace QWManage.Excel
{
    public class ExcelHelper
    {
        /// <summary>
        /// 导入 Excel 表格 to DataTable
        /// </summary>
        /// <param name="title"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string filePath)
        {
            if (string.IsNullOrEmpty(filePath)) return null;

            string fileName = Path.GetFileName(filePath);

            using (FileStream fs = File.OpenRead(filePath))
            {
                IWorkbook workbook = null;
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                ISheet sheet = workbook.GetSheetAt(0); // 获取此文件第一个Sheet页
                DataTable table = new DataTable();
                //获取sheet的首行
                IRow headerRow = sheet.GetRow(0);

                //一行最后一个方格的编号 即总的列数
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                //最后一行的标号  即总的行数
                int rowCount = sheet.LastRowNum;

                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                            {
                                dataRow[j] = cell.DateCellValue.ToString();//判断是不是日期
                            }
                            else
                            {
                                dataRow[j] = cell.ToString();
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                workbook = null;
                sheet = null;
                return table;
            }
        }

        public static decimal ValueTodecimal(object value)
        {
            return decimal.Parse(value.ToString());
        }


        public static void DataTableToExcel(List<DataTable> tables, string excelFilePath)
        {
            IWorkbook workbook;
            if (excelFilePath.EndsWith(".xlsx"))
            {
                workbook = new XSSFWorkbook();
            }
            else
            {
                workbook = new HSSFWorkbook(); // 对于.xls  
            }

            foreach (DataTable dt in tables)
            {
                ISheet sheet = workbook.CreateSheet(dt.TableName);

                // 创建表头  
                IRow headerRow = sheet.CreateRow(0);
                foreach (DataColumn column in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(column.Ordinal);
                    cell.SetCellValue(column.ColumnName);
                }

                // 填充数据  
                for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
                {
                    IRow row = sheet.CreateRow(rowNum + 1); // +1 因为表头占用了一行  
                    foreach (DataColumn column in dt.Columns)
                    {
                        ICell cell = row.CreateCell(column.Ordinal);
                        if (!Convert.IsDBNull(dt.Rows[rowNum][column]))
                        {
                            cell.SetCellValue(dt.Rows[rowNum][column].ToString());
                        }
                    }
                }
            }
            // 写入文件  
            using (FileStream file = new FileStream(excelFilePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(file);
            }
        }
    }
}